package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.DBConn;
import entities.GoodsEntity;
import entities.TgoodsEntity;

public class TgoodsService {

	// 获得团购前N个商品
	public static List<TgoodsEntity> getTgoods() throws SQLException {
		List<TgoodsEntity> tgoodsList = new ArrayList<TgoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_tgoods ,shop_seller,shop_goodsclass  where  shop_tgoods.tgoods_sellerId = shop_seller.seller_id "
				+ " and shop_tgoods.tgoods_classId = shop_goodsclass.class_id  ";

		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			TgoodsEntity tgoods = new TgoodsEntity();
			tgoods.setTgId(rs.getInt("tgoods_id"));
			tgoods.setTgClaId(rs.getInt("tgoods_classId"));
			tgoods.setTgName(rs.getString("tgoods_name"));
			tgoods.setTgSeId(rs.getInt("tgoods_sellerId"));
			tgoods.setTgSaCla(rs.getInt("tgoods_saleClass"));
			tgoods.setTgPrice(rs.getDouble("tgoods_price"));
			tgoods.setTgDis(rs.getDouble("tgoods_discount"));
			tgoods.setTgImage(rs.getString("tgoods_image"));
			tgoods.setTgNutri(rs.getString("tgoods_nutrition"));
			tgoods.setTgKeyWo(rs.getString("tgoods_keyWord"));
			tgoods.setTgDesc(rs.getString("tgoods_desc"));
			tgoods.setTgRank(rs.getDouble("tgoods_rank"));
			tgoods.setTgSaCou(rs.getInt("tgoods_saleCount"));
			tgoods.setSellerName(rs.getString("seller_name"));
			tgoods.setSellerAddr(rs.getString("seller_addr"));
			tgoods.setSellerTel(rs.getString("seller_tel"));
			tgoods.setTgOldPrice((int) (tgoods.getTgPrice() / rs
					.getDouble("tgoods_discount")));

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			tgoods.setTgSaTim(sdf.format(rs.getTimestamp("tgoods_onSaleTime")));

			tgoods.setTgPeoCou(rs.getInt("tgoods_peopleCount"));

			tgoods.setTgDeadline(sdf.format(rs.getTimestamp("tgoods_deadline")));

			tgoodsList.add(tgoods);

		}
		rs.close();
		stmt.close();
		conn.close();
		return tgoodsList;
	}

	// 获得某一类别的商品
	public static List<TgoodsEntity> getClassGoods(String classId, String limit)
			throws SQLException {
		List<TgoodsEntity> tGoodsList = new ArrayList<TgoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_tgoods,shop_seller,shop_goodsclass where tgoods_classId = class_id and tgoods_classId = "
				+ classId
				+ " and shop_tgoods.tgoods_sellerId = shop_seller.seller_id  order by rand() limit "
				+ limit;
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			TgoodsEntity tgoods = new TgoodsEntity();
			tgoods.setTgId(rs.getInt("tgoods_id"));
			tgoods.setTgClaId(rs.getInt("tgoods_classId"));
			tgoods.setTgName(rs.getString("tgoods_name"));
			tgoods.setTgSeId(rs.getInt("tgoods_sellerId"));
			tgoods.setTgSaCla(rs.getInt("tgoods_saleClass"));
			tgoods.setTgPrice(rs.getDouble("tgoods_price"));
			tgoods.setTgDis(rs.getDouble("tgoods_discount"));
			tgoods.setTgImage(rs.getString("tgoods_image"));
			tgoods.setTgNutri(rs.getString("tgoods_nutrition"));
			tgoods.setTgKeyWo(rs.getString("tgoods_keyWord"));
			tgoods.setTgDesc(rs.getString("tgoods_desc"));
			tgoods.setTgRank(rs.getDouble("tgoods_rank"));
			tgoods.setTgSaCou(rs.getInt("tgoods_saleCount"));
			tgoods.setSellerName(rs.getString("seller_name"));
			tgoods.setSellerAddr(rs.getString("seller_addr"));
			tgoods.setSellerTel(rs.getString("seller_tel"));

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			tgoods.setTgSaTim(sdf.format(rs.getTimestamp("tgoods_onSaleTime")));

			tgoods.setTgPeoCou(rs.getInt("tgoods_peopleCount"));

			tgoods.setTgDeadline(sdf.format(rs.getTimestamp("tgoods_deadline")));
			System.out.println(tgoods.toString());
			tGoodsList.add(tgoods);
		}
		stmt.close();
		conn.close();
		return tGoodsList;
	}

	// 获得热门团购的商品
	public static List<TgoodsEntity> getHotGoods() throws SQLException {
		List<TgoodsEntity> tGoodsList = new ArrayList<TgoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_tgoods ,shop_seller,shop_goodsclass where shop_tgoods.tgoods_sellerId = shop_seller.seller_id and shop_tgoods.tgoods_classId = shop_goodsclass.class_id order by tgoods_saleCount Desc limit 3";
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			TgoodsEntity tgoods = new TgoodsEntity();
			tgoods.setTgId(rs.getInt("tgoods_id"));
			tgoods.setTgClaId(rs.getInt("tgoods_classId"));
			tgoods.setTgName(rs.getString("tgoods_name"));
			tgoods.setTgSeId(rs.getInt("tgoods_sellerId"));
			tgoods.setTgSaCla(rs.getInt("tgoods_saleClass"));
			tgoods.setTgPrice(rs.getDouble("tgoods_price"));
			tgoods.setTgDis(rs.getDouble("tgoods_discount"));
			tgoods.setTgImage(rs.getString("tgoods_image"));
			tgoods.setTgNutri(rs.getString("tgoods_nutrition"));
			tgoods.setTgKeyWo(rs.getString("tgoods_keyWord"));
			tgoods.setTgDesc(rs.getString("tgoods_desc"));
			tgoods.setTgRank(rs.getDouble("tgoods_rank"));
			tgoods.setTgSaCou(rs.getInt("tgoods_saleCount"));
			tgoods.setSellerName(rs.getString("seller_name"));
			tgoods.setSellerAddr(rs.getString("seller_addr"));
			tgoods.setSellerTel(rs.getString("seller_tel"));

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			tgoods.setTgSaTim(sdf.format(rs.getTimestamp("tgoods_onSaleTime")));

			tgoods.setTgPeoCou(rs.getInt("tgoods_peopleCount"));

			tgoods.setTgDeadline(sdf.format(rs.getTimestamp("tgoods_deadline")));
			System.out.println(tgoods.toString());
			tGoodsList.add(tgoods);
		}
		stmt.close();
		conn.close();
		return tGoodsList;
	}

	// 获得团购前N个商品
	public static List<TgoodsEntity> getHotTgoods() throws SQLException {
		List<TgoodsEntity> tgoodsList = new ArrayList<TgoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_tgoods ,shop_seller,shop_goodsclass  where  shop_tgoods.tgoods_sellerId = shop_seller.seller_id "
				+ " and shop_tgoods.tgoods_classId = shop_goodsclass.class_id order by tgoods_saleCount desc limit 6  ";

		System.out.println(sql);
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			TgoodsEntity tgoods = new TgoodsEntity();
			tgoods.setTgId(rs.getInt("tgoods_id"));
			tgoods.setTgClaId(rs.getInt("tgoods_classId"));
			tgoods.setTgName(rs.getString("tgoods_name"));
			tgoods.setTgSeId(rs.getInt("tgoods_sellerId"));
			tgoods.setTgSaCla(rs.getInt("tgoods_saleClass"));
			tgoods.setTgPrice(rs.getDouble("tgoods_price"));
			tgoods.setTgDis(rs.getDouble("tgoods_discount"));
			tgoods.setTgImage(rs.getString("tgoods_image"));
			tgoods.setTgNutri(rs.getString("tgoods_nutrition"));
			tgoods.setTgKeyWo(rs.getString("tgoods_keyWord"));
			tgoods.setTgDesc(rs.getString("tgoods_desc"));
			tgoods.setTgRank(rs.getDouble("tgoods_rank"));
			tgoods.setTgSaCou(rs.getInt("tgoods_saleCount"));
			tgoods.setSellerName(rs.getString("seller_name"));
			tgoods.setSellerAddr(rs.getString("seller_addr"));
			tgoods.setSellerTel(rs.getString("seller_tel"));

			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			tgoods.setTgSaTim(sdf.format(rs.getTimestamp("tgoods_onSaleTime")));

			tgoods.setTgPeoCou(rs.getInt("tgoods_peopleCount"));

			tgoods.setTgDeadline(sdf.format(rs.getTimestamp("tgoods_deadline")));

			tgoodsList.add(tgoods);

		}
		rs.close();
		stmt.close();
		conn.close();
		return tgoodsList;
	}

	// 获得指定商品
	public static TgoodsEntity getPointTgoods(int id) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_tgoods , shop_seller, shop_goodsclass where shop_tgoods.tgoods_sellerId = shop_seller.seller_id and tgoods_classId = class_id and tgoods_id = "
				+ id;
		TgoodsEntity tgoods = new TgoodsEntity();
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			tgoods.setTgId(rs.getInt("tgoods_id"));
			tgoods.setTgClaId(rs.getInt("tgoods_classId"));
			tgoods.setTgName(rs.getString("tgoods_name"));
			tgoods.setTgSeId(rs.getInt("tgoods_sellerId"));
			tgoods.setTgSaCla(rs.getInt("tgoods_saleClass"));
			tgoods.setClassName(rs.getString("class_name"));
			tgoods.setTgPrice(rs.getDouble("tgoods_price"));
			tgoods.setTgDesc(rs.getString("tgoods_desc"));
			tgoods.setTgDis(rs.getDouble("tgoods_discount"));
			tgoods.setTgImage(rs.getString("tgoods_image"));
			tgoods.setTgRank(rs.getDouble("tgoods_rank"));
			tgoods.setTgSaCou(rs.getInt("tgoods_saleCount"));
			tgoods.setTgKeyWo(rs.getString("tgoods_keyWord"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			// goods.setOnSaleTime(sdf.format(rs.getDate("goods_onSaleTime")));
			tgoods.setTgSaTim(sdf.format(rs.getTimestamp("tgoods_onSaleTime")));
			tgoods.setTgPeoCou(rs.getInt("tgoods_peopleCount"));
			tgoods.setTgDeadline(sdf.format(rs.getTimestamp("tgoods_deadline")));
			tgoods.setSellerName(rs.getString("seller_name"));
			tgoods.setSellerAddr(rs.getString("seller_addr"));
			tgoods.setSellerTel(rs.getString("seller_tel"));

		}
		rs.close();
		stmt.close();
		conn.close();
		return tgoods;
	}

	public static int changeImageUrl(String oldUrl, String newUrl)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "update shop_tgoods set tgoods_image = '" + newUrl
				+ "_1.png' , tgoods_image2 = '" + newUrl
				+ "_2.png' , tgoods_image3 = '" + newUrl
				+ "_3.png' where tgoods_image ='" + oldUrl + "' ";
		int result = stmt.executeUpdate(sql);
		return result;
	}

	// 获得某一商家的团购商品
	public static List<TgoodsEntity> getSellerTgoods(String sellerId)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_tgoods , shop_seller, shop_goodsclass where shop_tgoods.tgoods_sellerId = shop_seller.seller_id and tgoods_classId = class_id and tgoods_sellerId = "
				+ sellerId;
		List<TgoodsEntity> tGoodsList = new ArrayList<TgoodsEntity>();
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			TgoodsEntity tgoods = new TgoodsEntity();
			tgoods.setTgId(rs.getInt("tgoods_id"));
			tgoods.setTgClaId(rs.getInt("tgoods_classId"));
			tgoods.setTgName(rs.getString("tgoods_name"));
			tgoods.setTgSeId(rs.getInt("tgoods_sellerId"));
			tgoods.setTgSaCla(rs.getInt("tgoods_saleClass"));
			tgoods.setTgPrice(rs.getDouble("tgoods_price"));
			tgoods.setTgDesc(rs.getString("tgoods_desc"));
			tgoods.setTgDis(rs.getDouble("tgoods_discount"));
			tgoods.setTgImage(rs.getString("tgoods_image"));
			tgoods.setTgRank(rs.getDouble("tgoods_rank"));
			tgoods.setTgSaCou(rs.getInt("tgoods_saleCount"));
			tgoods.setTgKeyWo(rs.getString("tgoods_keyWord"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			// goods.setOnSaleTime(sdf.format(rs.getDate("goods_onSaleTime")));
			tgoods.setTgSaTim(sdf.format(rs.getTimestamp("tgoods_onSaleTime")));
			tgoods.setTgPeoCou(rs.getInt("tgoods_peopleCount"));
			tgoods.setTgDeadline(sdf.format(rs.getTimestamp("tgoods_deadline")));
			tgoods.setSellerName(rs.getString("seller_name"));
			tgoods.setSellerAddr(rs.getString("seller_addr"));
			tgoods.setSellerTel(rs.getString("seller_tel"));
			tGoodsList.add(tgoods);

		}
		rs.close();
		stmt.close();
		conn.close();
		return tGoodsList;
	}

	// 下架某商品
	public static String offlineGoods(String sellerId, String goodsId)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt;
		stmt = conn.createStatement();

		String sql = "delete from shop_tgoods where tgoods_Id =" + goodsId
				+ " and tgoods_sellerId=" + sellerId;
		System.out.println(sql + "-------sql");
		int result = stmt.executeUpdate(sql);
		return result > 0 ? "success" : "failed";
	}

	// 得到新建团购商品图片的图片名称 OEM++liwt 2013-05-09
	public static String getNewTGoodsName(String sellerId) {
		Connection conn = DBConn.getConn();
		Statement stmt;
		String imgName = "seller" + sellerId.trim();

		try {
			stmt = conn.createStatement();
			String sql = "select tgoods_id from shop_tgoods order by tgoods_id desc limit 1";
			System.out.println(sql + "-------sql");
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				imgName += "tg" + (rs.getInt(1) + 1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return imgName;
	}

	// 用于往数据库中添加商品
	public static String addGoods(String sellerId, String goodsName,
			String classId, String price, String discount, String imgUrl,
			String keywords, String desc) throws SQLException {

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "INSERT INTO shop_tgoods VALUES (null, " + classId
				+ ", '" + goodsName + "', " + sellerId + ", 1, " + price + ","
				+ discount + ", '" + imgUrl + "_1.png', '好营养哦', '" + keywords
				+ "', '" + desc + "', 5.0, 0, '" + sdf.format(new Date())
				+ "' ,0,'"+sdf.format(new Date())+"','" + imgUrl + "_2.png','" + imgUrl + "_3.png');";
		int result = stmt.executeUpdate(sql);
		return result > 0 ? "success" : "failed";
	}

	public static void main(String[] args) throws SQLException {
		System.out.println(getTgoods());
		// System.out.println(getClassGoods("1", "6"));
		// System.out.println(changeImageUrl("1", "1", "imga"));
		System.out.println(getSellerTgoods("1"));
		System.out.println(offlineGoods("3", "7"));
		System.out.println(getNewTGoodsName("1"));
	}
}
